
create database TestDB

create table ClassInfo
(
	ClassId int identity(1,1) primary key,  --主键，班级编号，标识列
	ClassName nvarchar(20) not null			--班级名称，非空
)
go

--创建学生信息表
create table StuInfo
(
	StuId int identity(1,1) primary key,	--主键，学号，标识列
	ClassId int references ClassInfo(ClassId) on delete set null, --所属班级编号，外键关联班级表的班级编号
	StuName nvarchar(10) not null,		--姓名，非空
	StuSex  nvarchar(1) default('男') check(StuSex in('男','女')),	--性别
	StuBrithday date,	--出生日期
	StuPhone nvarchar(11) check(len(StuPhone)=11) unique,--手机号，限制11位,唯一不重复
	StuProvince nvarchar(200),--地址
	CreateDate datetime default(getdate())	--创建时间，默认为系统时间
)
go

--创建课程信息表
create table CourseInfo
(
	CourseId int identity(1,1) primary key,	--课程编号，主键，标识列
	CourseName nvarchar(50) unique not null ,--课程名称，非空，唯一不重复
	CourseCredit int default(1) check(CourseCredit between 1 and 5) --学分，默认值为1，取值范围1-5
)
go

--创建成绩表
create table Scores
(
	ScoreId int identity(1,1) primary key,--成绩编号，主键，标识列
	StuId int references StuInfo(StuId),--学号，外键关联学生信息表的学号
	CourseId int references CourseInfo(CourseId),--课程编号，外键关联课程信息表的课程编号
	Score int default(0)	--成绩,默认为0
)
go



----------------
--插入数据部分
----------------
--插入班级信息表
insert into ClassInfo(ClassName)
values('软件1班'),('软件2班'),('软件3班'),('软件4班'),('软件5班'),('软件6班'),('软件7班')
go


--插入学生信息
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(1,'刘正','男','2002-08-02','13245678121','广西省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(1,'黄贵','男','2003-07-02','13345678121','江西省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(1,'陈美','女','2002-07-22','13355678125','福建省')

insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(2,'江文','男','2001-07-02','13347678181','湖南省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(2,'钟琪','女','2003-01-13','13345778129','安徽省')


insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(3,'曾小林','男','2003-05-15','13345378563','安徽省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(3,'欧阳天天','女','2002-08-19','13347878121','湖北省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(3,'李逍遥','男','2003-09-02','13345678557','广东省')


insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(4,'刘德华','男','2003-06-11','15345679557',null)
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(4,'刘翔','男','2003-07-09','18346679589',null)
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(4,'曾小贤','男','2003-07-02','18348979589',null)


insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'刘','男','2002-07-02','18348979509',null)
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'陈天翔','男','2002-07-02','18348079509',null)
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'刘能','男','2002-08-02','13245678122','广西省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'钟馗','男','2002-08-02','13245678123','广西省')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'钟吴艳','女','2002-08-02','13245678124','广西省')

insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'刘欢','男','2002-07-02','13245678125',null)
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'张庭','女','2002-07-02','13245678126',null)
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'曹植','男','2002-08-02','13245678127','')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'曹操','男','2002-08-02','13245678128','')
insert into StuInfo(ClassId,StuName,StuSex,StuBrithday,StuPhone,StuProvince)
values(5,'孙尚香','女','2002-08-02','13245678129','')
go



--插入课程信息
insert into CourseInfo(CourseName,CourseCredit) values('计算机基础',3)
insert into CourseInfo(CourseName,CourseCredit) values('HTML+CSS网页制作',5)
insert into CourseInfo(CourseName,CourseCredit) values('JAVA编程基础',5)
insert into CourseInfo(CourseName,CourseCredit) values('SQL Server数据库基础',4)
insert into CourseInfo(CourseName,CourseCredit) values('C#面向对象编程',5)
insert into CourseInfo(CourseName,CourseCredit) values('Winform桌面应用程序设计',5)
go

--插入成绩信息
insert into Scores (StuId, CourseId, Score) values (1, 1, 80);
insert into Scores (StuId, CourseId, Score) values (1, 2, 78);
insert into Scores (StuId, CourseId, Score) values (1, 3, 65);
insert into Scores (StuId, CourseId, Score) values (1, 4, 90);

insert into Scores (StuId, CourseId, Score) values (2, 1, 60);
insert into Scores (StuId, CourseId, Score) values (2, 2, 77);
insert into Scores (StuId, CourseId, Score) values (2, 3, 68);
insert into Scores (StuId, CourseId, Score) values (2, 4, 88);

insert into Scores (StuId, CourseId, Score) values (3, 1, 88);
insert into Scores (StuId, CourseId, Score) values (3, 2, 45);
insert into Scores (StuId, CourseId, Score) values (3, 3, 66);
insert into Scores (StuId, CourseId, Score) values (3, 4, 75);

insert into Scores (StuId, CourseId, Score) values (4, 1, 56);
insert into Scores (StuId, CourseId, Score) values (4, 2, 80);
insert into Scores (StuId, CourseId, Score) values (4, 3, 75);
insert into Scores (StuId, CourseId, Score) values (4, 4, 66);

insert into Scores (StuId, CourseId, Score) values (5, 1, 88);
insert into Scores (StuId, CourseId, Score) values (5, 2, 79);
insert into Scores (StuId, CourseId, Score) values (5, 3, 72);
insert into Scores (StuId, CourseId, Score) values (5, 4, 85);

insert into Scores (StuId, CourseId, Score) values (6, 1, 68);
insert into Scores (StuId, CourseId, Score) values (6, 2, 88);
insert into Scores (StuId, CourseId, Score) values (6, 3, 73);
insert into Scores (StuId, CourseId, Score) values (6, 5, 63);

insert into Scores (StuId, CourseId, Score) values (7, 1, 84);
insert into Scores (StuId, CourseId, Score) values (7, 2, 90);
insert into Scores (StuId, CourseId, Score) values (7, 3, 92);
insert into Scores (StuId, CourseId, Score) values (7, 5, 78);

insert into Scores (StuId, CourseId, Score) values (8, 1, 58);
insert into Scores (StuId, CourseId, Score) values (8, 2, 59);
insert into Scores (StuId, CourseId, Score) values (8, 3, 65);
insert into Scores (StuId, CourseId, Score) values (8, 5, 75);

insert into Scores (StuId, CourseId, Score) values (9, 1, 48);
insert into Scores (StuId, CourseId, Score) values (9, 2, 67);
insert into Scores (StuId, CourseId, Score) values (9, 3, 71);
insert into Scores (StuId, CourseId, Score) values (9, 5, 56);
insert into Scores (StuId, CourseId, Score) values (9, 5, 56);
go

-------------------------------------
--对查询结果进行排序
----------------------------------
--查询成绩信息，成绩降序排列
select * from Scores order by  Score desc
--查询成绩信息，先按照课程编号排序，再进行成绩的降序排列
select * from Scores order by ScoreId desc,Score desc
--查询学号为1的成绩信息，并将结果按照成绩降序排列
select * from Scores where StuId=1 order by Score desc


--------------------------------------------------
--聚合函数
--------------------------------------------------
--1.查询学号为1的所有课程的总成绩
	select sum(Score) 总成绩 from Scores where Stuid=1
--2.查询成绩表中所有成绩的总和
	select sum(Score) 总成绩 from Scores
--3.查询学号为1的平均成绩
	select avg(Score) 平均成绩 from Scores
--4.查询课程编号为1的最高成绩信息
	select max(score) 最高成绩 from Scores where Stuid=1

--5.查询课程编号为1的最低成绩信息
	select min(Score) 最低成绩 from Scores where Stuid=1

--6.查询学号为1的学生参加的考试次数
	select count(*) 参加考试次数 from Scores Where Stuid=1


--7.查询课程编号为1的成绩统计信息：参考人数信息、总分、平均分、最高分、最低分
	select count(*) 参考人数信息,sum(Score) as 总分, avg(Score) 平均分,max(Score) 最高分,min(Score) 最低分 from Scores where Stuid=1

--8.查询学号为1的学生的成绩统计信息：总分、平均分、最高分、最低分
	select sum(Score) 总分,avg(Score) 平均分,max(Score) 最高分,min(Score) 最低分 from Scores where Stuid=1


-------------------------------------------------------
--分组查询
--------------------------------------------------------
--9.查询每个学生的总成绩
	select sum(Score) 总成绩  from Scores group by Stuid
--10.查询每个学生的成绩统计信息：参考次数、总分、平均分、最高分
	select count(*) 参考次数,sum(Score) 总分,avg(Score) 平均分,max(Score) 最高分 from Scores group by Stuid
--11.查询每门课程的成绩统计信息：参考人数信息、总分、平均分、最高分、最低分
	select count(*) 参考次数,Sum(Score) 总分, avg(Score)平均分,max(Score) 最高分,min(Score) 最低分 from Scores group by CourseId
--12.查询学号1-5的学生的总成绩信息
	select Sum(Score) 总成绩 From Scores Where Stuid between 1 and 5 
--13.根据学号进行分组统计总分信息，只显示总分大于300的信息
	select Stuid 学号,sum(Score) as 总分 from Scores group by  Stuid having sum(Score)>300
--14.根据学号进行分组统计平均分信息，只显示平均分小于60的信息
	select Stuid 学号,avg(Score) as 平均分数 from Scores group by Stuid Having avg(Score)<60

-------------------------------------------
--对查询结果限制行数
------------------------------------------
--查询成绩表中学号信息
	select Stuid from Scores
--查询成绩表中最高分的成绩信息
	select max(Score) as 最高分 from Scores
--查询成绩表中的前10条数据
	select top 10* from Scores
--查询排名在前10%的学生成绩
	Select top 10 percent * from Scores
--获取成绩表中第11-20条的记录
	select * from Scores where ScoreId between 11 and 20 